-- =============================================
-- Procedure: GetClosedAddonLoans
-- Purpose:	Gets a list of addon loans that have been closed early within a specified period 
-- Author: Dale McFarlane
-- Created: 2014-1-29
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  
-- =====================================================================================
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetClosedAddonLoans]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[GetClosedAddonLoans]
END
GO
/*
	DECLARE @from_date datetime
	DECLARE @to_date datetime
	SET @from_date = CAST('2009-05-25' AS DATETIME)
	SET @to_date = CAST('2014-05-25' AS DATETIME)
	EXEC GetClosedAddonLoans @from_date, @to_date
*/
CREATE PROCEDURE [dbo].[GetClosedAddonLoans]
(
	@from_report_date DATETIME,
	@to_report_date DATETIME
)
AS
BEGIN

	DECLARE @from_report_date_str VARCHAR(20)
	
	IF @to_report_date IS NOT NULL
	BEGIN
		SET @to_report_date = DATEADD(HOUR,23,@to_report_date)
		SET @to_report_date = DATEADD(MINUTE,59,@to_report_date)
		SET @to_report_date = DATEADD(SECOND,59,@to_report_date)
		PRINT 'To Date: '+CAST(@to_report_date AS VARCHAR(30))
	END

	IF @from_report_date IS NOT NULL
	BEGIN
		SET @from_report_date_str = CONVERT(varchar(30), @from_report_date, 101)
		SET @from_report_date =  CONVERT(DATETIME, @from_report_date, 101)
		PRINT 'From Date: '+CAST(@from_report_date AS VARCHAR(30))
	END
	
	SELECT 
	L.loan_no,
	L.principal,
	L.start_date,
	L.end_date,
	L.closed_date,
	L.term,
	L.period,
	L.product,
	L.interest_rate
	FROM
		loan_addon  L
	WHERE
		L.closed_date >= @from_report_date AND
		L.closed_date <= @to_report_date 
			
END

GO


